﻿using System;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace Citi.ExpenseReport.WebUI.Util
{
    public class ExcelReader : IDisposable
    {
        private readonly bool _useConnectionString;
        private readonly OleDbConnection _connection;

        #region Constructors

        public ExcelReader(string path)
            : this(path, true, true)
        {
        }

        public ExcelReader(string path, bool intermixedRead)
            : this(path, intermixedRead, true)
        {
        }

        public ExcelReader(string path, bool intermixedRead, bool firstRowIsHeader)
        {
            if (string.IsNullOrEmpty(path))
                throw new ArgumentException("path");

            _connection = ExcelHelper.CreateConnection(path, intermixedRead, firstRowIsHeader);
        }

        public ExcelReader(string path, bool useConnectionString, bool intermixedRead, bool firstRowIsHeader,
                           bool isOledb12)
        {
            _useConnectionString = useConnectionString;
            if (string.IsNullOrEmpty(path))
                throw new ArgumentException("path");

            _connection = isOledb12
                              ? ExcelHelper.CreateConnectionOledb12(path, intermixedRead, firstRowIsHeader)
                              : ExcelHelper.CreateConnection(path, intermixedRead, firstRowIsHeader);
        }

        #endregion

        #region Destructors

        public void Dispose()
        {
            if (_connection == null) return;

            if (_connection.State != ConnectionState.Closed)
            {
                try
                {
                    _connection.Close();
                }
                catch
                {
                }
            }
            _connection.Dispose();
        }

        #endregion

        #region GetData Members

        /// <summary>
        /// Obtém um DataTable com os dados do arquivo.
        /// </summary>
        /// <returns>Um <see cref="System.Data.DataTable"/> formado a partir do arquivo.</returns>
        public DataTable GetData()
        {
            string[] fileSheets = GetSheetNames();
            return GetData(fileSheets[0]);
        }

        /// <summary>
        /// Obtém um DataTable com os dados do arquivo.
        /// </summary>
        /// <param name="sheetName">Nome da aba a ser selecionada.</param>
        /// <returns>Um <see cref="System.Data.DataTable"/> formado a partir do arquivo.</returns>
        public DataTable GetData(string sheetName)
        {
            return GetData(sheetName, false);
        }

        /// <summary>
        /// Obtém um DataTable com os dados do arquivo.
        /// </summary>
        /// <param name="sheetName">Nome da aba a ser selecionada.</param>
        /// <param name="ignoreNullRows">Não retorna registros completamente nulos na consulta.</param>
        /// <returns>Um <see cref="System.Data.DataTable"/> formado a partir do arquivo.</returns>
        public DataTable GetData(string sheetName, bool ignoreNullRows)
        {
            if (string.IsNullOrEmpty(sheetName))
                throw new ArgumentException("sheetName");

            var dataTable = new DataTable(sheetName);

            var ignoreStatement = "";
            if (ignoreNullRows)
            {
                var columns = ExcelHelper.GetSheetDataColumns(_connection, sheetName);

                var sbIgnore = new StringBuilder();
                foreach (var column in columns)
                {
                    sbIgnore.Append(sbIgnore.Length == 0 ? " WHERE " : " OR ");

                    sbIgnore.Append("[");
                    sbIgnore.Append(column.ColumnName);
                    sbIgnore.Append("]");

                    sbIgnore.Append(" IS NOT NULL");
                }
                ignoreStatement = sbIgnore.ToString();
            }

            var sheetCommand =
                new OleDbCommand(
                    String.Format("SELECT * FROM [{0}$] {1}", sheetName.Replace("$", ""), ignoreStatement), _connection);
            var sheetAdapter = new OleDbDataAdapter(sheetCommand);

            sheetAdapter.Fill(dataTable);

            return dataTable;
        }

        #endregion

        #region GetSheetNames Members

        public string[] GetSheetNames()
        {
            return ExcelHelper.GetSheetNames(_connection);
        }

        public DataColumn[] GetSheetDataColumns()
        {
            var fileSheets = GetSheetNames();
            return GetSheetDataColumns(fileSheets[0]);
        }

        public DataColumn[] GetSheetDataColumns(string sheetName)
        {
            if (string.IsNullOrEmpty(sheetName))
                throw new ArgumentException("sheetName");

            return ExcelHelper.GetSheetDataColumns(_connection, sheetName);
        }

        #endregion
    }
}